﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;

namespace BLL.SchemaDiscovery
{
    public partial class SqlServerManager
    {
        public static List<string> ListDbs(
            string serverName, string userName, string password)
        {
            string connStr = string.Format("Server={0};Database=Master;User ID={1};Password={2};", serverName, userName, password);
            string[] dbs = DbUtil.GetDatabases(connStr);
            List<string> dbNames = new List<string>();
            dbNames.AddRange(dbs);
            return dbNames;
        }

        public static List<string> ListDbSchemas(
            string serverName, string dbName, string userName, string password)
        {
            string connStr = string.Format("Server={0};Database={1};User ID={2};Password={3}", serverName, dbName, userName, password);
            string[] schemas = DbUtil.GetSchemaNames(DataProviderType.MSSQL, connStr);
            List<string> schemaNames = new List<string>();
            schemaNames.AddRange(schemas);
            return schemaNames;
        }

        public static List<string> ListTables(
            string serverName, string dbName, string userName, string password, 
            string schemaName)
        {
            string connStr = string.Format("Server={0};Database={1};User ID={2};Password={3}", serverName, dbName, userName, password);
            Dictionary<string, List<string>> schemaTbls =
                DbUtil.GetTableNamesWithSchemas(DataProviderType.MSSQL, connStr);
            List<string> tblNames = new List<string>();
            tblNames.AddRange(schemaTbls[schemaName]);
            return tblNames;
        }

        public static List<string> ListTableColumnNames(
            string serverName, string dbName, string userName, string password,
            string schemaName, string tblName)
        {
            string connStr = string.Format("Server={0};Database={1};User ID={2};Password={3}", serverName, dbName, userName, password);
            string[] cols = DbUtil.GetTableColumnNames(DataProviderType.MSSQL, connStr, schemaName, tblName);
            List<string> colNames = new List<string>();
            colNames.AddRange(cols);
            return colNames;
        }

        public static List<string> ListTableColumnNamesUsingSQL(
            string serverName, string dbName, string userName, string password,
            string sql)
        {
            string connStr = string.Format("Server={0};Database={1};User ID={2};Password={3}", serverName, dbName, userName, password);
            TableColumn[] cols = DbUtil.GetTableColumns(connStr, sql);
            List<string> colNames = new List<string>();
            foreach (TableColumn col in cols)
            {
                colNames.Add(col.ColumnName);
            }
            return colNames;
        }

        public static List<ColumnNameType> ListTableColumns(
            string serverName, string dbName, string userName, string password,
            string schemaName, string tblName)
        {
            string connStr = string.Format("Server={0};Database={1};User ID={2};Password={3}", serverName, dbName, userName, password);
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, schemaName, tblName);
            List<ColumnNameType> colDbTypes = new List<ColumnNameType>();
            foreach (TableColumn col in cols)
            {
                SimpleDataType dbType = SimpleDataTypeUtil.ToSimpleDataType(col.DbType);
                ColumnNameType columnNameType = new ColumnNameType()
                {
                    ColumnName = col.ColumnName,
                    DataType = (int)dbType,
                    Size = col.Size
                };
                colDbTypes.Add(columnNameType);
            }
            return colDbTypes;
        }
    }
}
